package com.taxsearch.controller;

import com.taxsearch.util.roomutil.PropertyUtil;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;


/**
 * 
 * @Title:DBUtil
 * @Description: 连接数据库工具类
 * @author 高敏
 * @version V1.0
 * @date 2018年8月16日
 */
public class DbbUtil {
	private static Connection connection = null;
	private static PreparedStatement smt = null;
	private static ResultSet rs = null;
	
	//链接数据库
	public static Connection getConnection() {
		String USERNAMR = PropertyUtil.getProperty("jdbc_username").trim();
		String PASSWORD = PropertyUtil.getProperty("jdbc_password").trim();
		String DRVIER = PropertyUtil.getProperty("jdbc_driverClassName").trim();
		String URL = PropertyUtil.getProperty("jdbc_url").trim();
		//String URL = "jdbc:oracle:thin:@192.168.88.160:1521:CDFGSDB";
		try {
			Class.forName(DRVIER);
			connection = DriverManager.getConnection(URL, USERNAMR, PASSWORD);
			System.out.println("成功连接数据库");
		} catch (ClassNotFoundException e) {
			System.out.println("ClassNotFound");
		} catch (SQLException e) {
			System.out.println("连接分发库失败");
		}
		return connection;

	}
	
	//关闭连接
	public static void close(Connection connectionTemp){
//		if (rs != null) {
//            try {
//                rs.close();
//            } catch (SQLException e) {
//                e.printStackTrace();
//            }
//        }
//        if (smt != null) {
//            try {
//                smt.close();
//            } catch (SQLException e) {
//                e.printStackTrace();
//            }
//        }
        if (connectionTemp != null) {
            try {
            	connectionTemp.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
		
	}
	
	//插入无证入住信息
	public static int insertCheckIn(Connection connection,HashMap<String, Object> map) {
    	String sql=
    			"insert into YPT_CGUESTER ("+
				"DJ_LVKEDAIMA,"+
				"DJ_XINGMING,"+
				"DJ_XINGBIE,"+
				"DJ_MINZU,"+
				"DJ_CHUSHENGRIQI,"+
				"DJ_ZHENGJIANLEIXING,"+
				"DJ_ZHENGJIANHAOMA,"+
				"DJ_SHENGSHIXIAN,"+
				"DJ_XIANGZHI,"+
				"DJ_RUZHUSHIJIAN,"+
				"DJ_RUZHUFANGHAO,"+
				"DJ_ZHAOPIAN,"+
				"DJ_UPZHAOPIAN,"+
				"DJ_LVGUANDAIMA,"+
				"BUPLOAD,"+
				"RECORDSNO,"+
				"STATIONID,"+
				"SAVETIME,"+
				"UPLOADTIME,"+
				"PACKNAME"+
				")"+
				"values('"+
				map.get("DJ_LVKEDAIMA")+"','"+
				map.get("DJ_XINGMING")+"','"+
				map.get("DJ_XINGBIE")+"','"+
				map.get("DJ_MINZU")+"',"+
				"to_date('"+map.get("DJ_CHUSHENGRIQI")+"','yyyy-mm-dd hh24:mi:ss'),'"+
				map.get("DJ_ZHENGJIANLEIXING")+"','"+
				map.get("DJ_ZHENGJIANHAOMA")+"','"+
				map.get("DJ_SHENGSHIXIAN")+"','"+
				map.get("DJ_XIANGZHI")+"',"+
				"to_date('"+map.get("DJ_RUZHUSHIJIAN")+"','yyyy-mm-dd hh24:mi:ss'),'"+
				map.get("DJ_RUZHUFANGHAO")+"','"+
				map.get("DJ_ZHAOPIAN")+"','"+
				map.get("DJ_UPZHAOPIAN")+"','"+
				map.get("DJ_LVGUANDAIMA")+"','"+
				map.get("BUPLOAD")+"','"+
				map.get("RECORDSNO")+"','"+
				map.get("STATIONID")+"',"+
				"SYSDATE,"+
				"SYSDATE,'"+
				map.get("PACKNAME")+"')";
    		System.out.println(sql);
        	try {
				smt=connection.prepareStatement(sql);
				int n = smt.executeUpdate();
				return n;
			} catch (SQLException e) {
				e.printStackTrace();
			}finally{
				close(connection);
			}
        	return 0;
			
	}
	
	//更改预定状态
	public static int updateYuDing(Connection connection,String STATIONID,String ROOMNUMBER){
		connection=getConnection();
		String insertString="update ROOM_BOOK set ZT='1'"+
				"where STATIONID = '"+STATIONID+"'"+
				"and ROOMNUMBER =  '"+ROOMNUMBER+"'"+
				"and ZT='0'";
		PreparedStatement smt;
		try {
			smt = connection.prepareStatement(insertString);
			int n = smt.executeUpdate();
			return n;
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			close(connection);
		}
		return 0;
	}
	
	/**
	 * @throws SQLException 
	 * @Title: updateCheckInBUPLOAD
	 * @Description: 无证审批成功将入住表BUPLOAD变成1未上传
	 * @param: @param id旅馆编号
	 * @param: @param list需要改变的列表
	 * @param: @return  
	 * @return: Integer   
	 * @throws
	 */
	public static Integer updateCheckInBUPLOAD(Connection connection,String id,List<HashMap<String, String>> list) throws SQLException{
		String str = "'0000'";
		for(int i=0;i<list.size();i++){
			str += ",'"+list.get(i).get("ID")+"'";
		}
		String sql = "update ypt_cguester t set t.BUPLOAD = 0 where t.BUPLOAD = 3 AND t.STATIONID='"+id+
				"' AND t.RECORDSNO in("+str+")";
		smt=connection.prepareStatement(sql);
		Integer n = smt.executeUpdate();
		return n;
	}
}
